package com.study4j.common.poi.util;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

/**
 * @description  poi的excel工具
 * @author jiangbin   
 * @date 2019年1月13日 下午8:22:16 
 * @version 1.0.0  
 */
public class ExcelUtil {
	private static final String EXCEL_XLS = "xls";
	private static final String EXCEL_SLSX = "xlsx";
		
	private static final Logger log = LoggerFactory.getLogger(ExcelUtil.class);

	/** 
	 * @description 判断excel的版本，获取workbook
	 * @param in InputStream输入流
	 * @param file 文件对象
	 * @author jiangbin
	 * @date 2019年1月12日 下午9:21:44
	 * @version 1.0.0
	 */
	public static Workbook getWorkbook(InputStream in,File file) throws IOException {
		Workbook wb = null; 
		if(file.getName().toLowerCase().endsWith(EXCEL_XLS)) {
			wb = new HSSFWorkbook(in);
		}else if(file.getName().toLowerCase().endsWith(EXCEL_SLSX)){
			wb = new XSSFWorkbook(in);
		}
		return wb;
	}
	
	/** 
	 * @description 判断文件是否为excel文件
	 * @param file 要判断的文件
	 * @return 判断结果
	 * @author jiangbin
	 * @date 2019年1月12日 下午9:27:42
	 * @version 1.0.0
	 */
	public static boolean checkExcelVaild(File file) {
		if(!file.exists()) {
			log.warn("要判断的excel文件不存在！");
			return false;
		}
		if(!file.isFile()) {
			log.warn("要判断的excel文件不是文件！");
			return false;
		}
		if(!(file.getName().toLowerCase().endsWith(EXCEL_SLSX) || file.getName().toLowerCase().endsWith(EXCEL_XLS))) {
			log.warn("要判断的文件不是excel文件！");
			return false;
		}
		return true;
	}
	
	private static Object getValue(Cell cell) {
		Object object = null;
		switch (cell.getCellType()) {
		case BOOLEAN:
			object = cell.getBooleanCellValue();
			break;
		case ERROR:
			object = cell.getErrorCellValue();
			break;
		case NUMERIC:
			object = cell.getNumericCellValue();
			break;
		case STRING:
			object = cell.getStringCellValue();
			break;
		default:
			break;
		}
		return object;
	}
	
	
	public static void main(String[] args) throws IOException {
		File file = new File("C:\\Users\\lenovo\\Downloads\\中南大学2018年硕士研究生招生报录情况统计.XLS");
		FileInputStream inputStream = new FileInputStream(file);
		checkExcelVaild(file);
		Workbook workbook = getWorkbook(inputStream, file);
		int sheetCount = workbook.getNumberOfSheets();
		System.out.println("sheet数量："+sheetCount);
		Sheet sheet = workbook.getSheetAt(0);
		int rowNum = sheet.getLastRowNum();
		System.out.println("总行数："+rowNum);
		for (Row row:sheet) {
			for (int i = 0,length = row.getLastCellNum(); i < length; i++) {
				Object value = getValue(row.getCell(i));
				System.out.print(value+"         ");
			}
			System.out.println();
		}
	}
}
















